package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import util.Properties;


public class DAO {

	private Connection conn;
	private PreparedStatement select_store_fields;
	private PreparedStatement select_stores;
	private PreparedStatement select_store_properties;
	private PreparedStatement insert_item; 
	private PreparedStatement insert_item_fields;
	private PreparedStatement select_property;
	
	public DAO(String db_host, String db_name, String db_user, String db_pass) {  
		
		try {
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = DriverManager.getConnection ("jdbc:mysql://"+db_host+"/"+db_name, db_user, db_pass);
			select_store_fields = conn.prepareStatement("SELECT * FROM store_fields WHERE store_id = ?");
			select_store_properties = conn.prepareStatement("SELECT * FROM store_properties WHERE store_id = ?");
			select_property = conn.prepareStatement("SELECT * FROM store_properties WHERE property = ?");
			select_stores = conn.prepareStatement("SELECT * from stores");
			insert_item =  conn.prepareStatement("INSERT INTO items (item_id, store_id, crawl_id, item_name, item_url, timestamp) VALUES (?,?,?,?,?,?)");
			insert_item_fields = conn.prepareStatement("INSERT INTO item_fields (crawl_id, item_id, field_id, value) VALUES (?,?,?,?)");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public List<Map<String,String>> getStoreFields(String storeId){
		List<Map<String,String>> fields = new ArrayList<Map<String,String>>();
		try {
			select_store_fields.setString(1, storeId);
			ResultSet rs = select_store_fields.executeQuery(); 
			while (rs.next()){
				Map<String,String> field = new HashMap<String,String>();
				field.put("field_id", rs.getString("field_id"));
				field.put("type", rs.getString("type"));
				field.put("selector", rs.getString("selector"));
				field.put("default", rs.getString("default"));
				field.put("mandatory", rs.getString("mandatory"));
				fields.add(field);
			}
		} catch (SQLException e) {
			
		}
		return fields;
	}
	public Properties getProperties(String storeId){
		Properties properties = new Properties();
		
		try {
			select_store_properties.setString(1, storeId);
			ResultSet rs = select_store_properties.executeQuery();
			while(rs.next()){
				properties.put(rs.getString("property"), rs.getString("value"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return properties;
		
	}
	public Map<String,String> getProperty(String property) throws SQLException{
		Map<String,String> map = new HashMap<String,String>(); 
		select_property.setString(1, property);
		ResultSet rs = select_property.executeQuery(); 
		while(rs.next()){
			map.put(rs.getString("store_id"),rs.getString("value"));
		}
		return map;
	}
	
	public Map<String,String> getStores() throws SQLException{
		Map<String,String> map = new HashMap<String,String>(); 
		
		ResultSet rs = select_stores.executeQuery(); 
		while(rs.next()){
			map.put(rs.getString("store_id"),rs.getString("store_name"));
		}
		return map;
	}
	
	public List<Map<String,String>> getSelectors(String source){
		List<Map<String,String>> fields = new ArrayList<Map<String,String>>();
        /*
         * populate selectors from db: field / type / selector / default
         */
        try {
			ResultSet rs = conn.createStatement().executeQuery("select * from fields where source_id = '"+source+"'");
			while (rs.next()){
				Map<String,String> field = new HashMap<String,String>();
				field.put("field", rs.getString("field"));
				field.put("type", rs.getString("type"));
				field.put("selector", rs.getString("selector"));
				field.put("default", rs.getString("default"));
				field.put("mandatory", rs.getString("mandatory"));
				fields.add(field);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
        return fields;
	}
	
	public void addItem(String itemId, String storeId, String crawlId, String name, String url){
		
		Timestamp timestamp = new Timestamp((new Date()).getTime());
		
		try {
			insert_item_fields.setString(1, itemId);
			insert_item_fields.setString(2, storeId);
			insert_item_fields.setString(3, crawlId);
			insert_item_fields.setString(4, name);
			insert_item_fields.setString(5, url);
			insert_item_fields.setTimestamp(6, timestamp);
			insert_item_fields.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public void addField(String crawlId, String itemId, String fieldId, String value){
		try {
			insert_item.setString(1, crawlId);
			insert_item.setString(2, itemId);
			insert_item.setString(3,fieldId);
			insert_item.setString(4,value);
			insert_item.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
